from flask import Blueprint, jsonify, request, json
import pymysql as sql

volunteer = Blueprint('volunteer', __name__)

connect = sql.Connect(
    host='bj-cdb-hjbj4oh0.sql.tencentcdb.com',
    port=60670,
    user='root',
    passwd='Yuan2000_',
    db='tencentmysql',
    charset='utf8'
)
cursor = connect.cursor()


@volunteer.route('/volunteerInfo', methods=['GET'])
def employeeInfo():
    sql = "SELECT id, `name`, gender, phone,checkin_date,ISACTIVE,imgset_dir FROM volunteer_info "
    print(sql)
    cursor.execute(sql)

    attribute = ['id', 'username', 'gender', 'phone', 'hire_date', 'ISACTIVE', 'imgset_dir']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)


@volunteer.route('/volunteerInfoBySearch', methods=['POST'])
def employeeInfoBySearch():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    username = json_data.get('username')
    sql = "SELECT id,name, gender, phone,checkin_date,ISACTIVE FROM volunteer_info where name ='" + username + "'"
    cursor.execute(sql)

    attribute = ['id', 'username', 'gender', 'phone', 'hire_date', 'ISACTIVE']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)


@volunteer.route('/newvolunteer', methods=['POST'])
def newemployee():
    print("new a newemployee")
    data = request.get_data()
    print(data)
    json_data = json.loads(data.decode("utf-8"))
    print(json_data)
    name = json_data.get('name')
    gender = json_data.get('gender')
    phone = json_data.get('phone')
    hire_date = json_data.get('hire_date')
    ISACTIVE = json_data.get('ISACTIVE')
    sql = "insert into volunteer_info  (`name`, `gender`, `phone`, `checkin_date` ,`ISACTIVE`) values( '" + name + "', '" + gender + "','" + phone + "','" + hire_date + "','" + ISACTIVE + "')"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(2)
    else:
        return jsonify(1)


@volunteer.route('/volunteerdelete', methods=['POST'])
def employeeDelete():
    data = request.get_data()
    print(data)
    word = data.decode("utf-8")
    print(word)
    sql = "Delete FROM volunteer_info WHERE id='" + str(word) + "'"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(2)
    else:
        return jsonify(1)


@volunteer.route('/editvol', methods=['POST', 'OPTIONS'])
def editacc():
    print("new a accTypepany")
    data = request.get_data()
    print(data)
    json_data = json.loads(data.decode("utf-8"))
    print(json_data)
    id = json_data.get('id')
    name = json_data.get('name')
    gender = json_data.get('gender')
    phone = json_data.get('phone')
    hire_date = json_data.get('hire_date')
    ISACTIVE = json_data.get('ISACTIVE')

    sql = "update `volunteer_info` set `name`='" + name + "',`gender`='" + gender + "',`phone`='" + phone + "',`checkin_date`='" + hire_date + "',`ISACTIVE`='" + ISACTIVE + "' where (`id`='" + id + "')"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(2)
    else:
        return jsonify(1)


@volunteer.route('/volunteerage', methods=['GET'])
def volunteerage():
    sql = "SELECT id, age,checkin_date,checkout_date FROM volunteer_info "
    cursor.execute(sql)

    attribute = ['id', 'age', 'checkin_date', 'checkout_date']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)
